<?php

/**
 * group model 文件
 * @author lijie
 * @date 2016-02-15
 */
class M_Quantification_Map extends MY_Model {
	
		public $table = "t_quantification_map";
	    function M_group() {
	        parent::__construct();
	    }
        //干部网格巡视详情
	    public function get_leader($dept_id,$where,$type=0,$limit='',$page=0)
        {
            if($type== 0)
            {
                $sql ="select count(um.PID)as num,count(IF(um.status=2,true,null)) as wnum,u.UserName from bjnz_new_quantification_user_map um LEFT JOIN bjnz_new_quantification_user us on um.qu_user_id=us.id LEFT JOIN t_user u ON um.PID=u.PID LEFT JOIN t_enterprise_dept d ON u.DepartmentID=d.ID where us.status=1 $where and u.DepartmentID in($dept_id) AND um.PID<>3008 GROUP BY um.PID";
            }
            else
            {

                if($page==1)
                {
                    $sql ="select count(*) as count
                        FROM
                        bjnz_new_quantification_user_map um
                        LEFT JOIN bjnz_new_quantification_user us ON um.qu_user_id = us.id
                        LEFT JOIN t_user u ON um.PID = u.PID
                        WHERE us.status = 1 $where";
                }
                else
                {
                    $sql ="select t1.*,t2.addtime from
                        (
                                SELECT
                                    um.id,
                                    u.LoginName,
                                    q. NAME AS quname,
                                    u.PID,
                                    d. NAME,
                                    um.end_number,
                                    map.area,
                                    um.number,
                                    um.start_time,
                                    um.end_time,
                                    u.UserName
                                FROM
                                    bjnz_new_quantification_user_map um
                                LEFT JOIN bjnz_new_quantification_user us ON um.qu_user_id = us.id
                                LEFT JOIN bjnz_new_quantification q ON us.qu_id = q.id
                                LEFT JOIN t_user u ON um.PID = u.PID
                                LEFT JOIN t_enterprise_dept d ON u.DepartmentID = d.ID
                                LEFT JOIN t_bjnz_map map ON map.id = um.map_id
                                WHERE
                                    us. STATUS = 1 $where
                                GROUP BY um.id
                                ORDER BY um.id DESC
                                    ) t1
                                LEFT JOIN
                                 (
                                    SELECT
                                            um.id,
                                            MAX(addss.addtime) as addtime
                                    FROM
                                        bjnz_new_quantification_user_map_add addss
                                    LEFT JOIN bjnz_new_quantification_user_map um ON addss.qum_id = um.id
                                        LEFT JOIN bjnz_new_quantification_user uss ON um.qu_user_id = uss.id
                                        LEFT JOIN t_user u ON um.PID = u.PID
                                    WHERE
                                        uss.status=1 $where
                                GROUP BY um.id
                                ORDER BY um.id DESC
                                ) t2
                                ON t1.id=t2.id
                                GROUP BY t1.id
                                ORDER BY t2.addtime desc $limit";
                }
            }
            $result = $this->querylist($sql);
            return $result;
        }
        //职工网格巡视详情
        public function get_worker($start,$end,$where,$type=0,$limit='',$page=0)
        {
            if($type==0)
            {
                $sql ="SELECT count(um.PID) AS num, count(IF(um. STATUS = 2, TRUE, NULL)) AS wnum, u.UserName FROM bjnz_new_quantification_user_map um LEFT JOIN bjnz_new_quantification_user us ON um.qu_user_id = us.id LEFT JOIN t_user u ON um.PID = u.PID LEFT JOIN t_enterprise_dept d ON u.DepartmentID = d.ID WHERE us. STATUS = 2 $where GROUP BY um.PID";
            }
            else
            {
                $sql ="select u.LoginName,u.PID,um.status,if(q.user_type=3,w.name,'') as gname,um.id,adds.addtime,q. NAME AS quname, adds.qum_id,d.NAME as buname, um.end_number, map.area, um.number, um.start_time, um.end_time, u.UserName 
                        from bjnz_new_quantification_user_map um
                        LEFT JOIN bjnz_new_quantification_user us ON um.qu_user_id = us.id
                        LEFT JOIN bjnz_new_quantification q ON us.qu_id=q.id
                        LEFT JOIN bjnz_new_quantification_user_map_add adds ON um.id = adds.qum_id
                        LEFT JOIN t_user u ON um.PID = u.PID
                        LEFT JOIN t_enterprise_dept d ON u.DepartmentID = d.ID
                        LEFT JOIN t_bjnz_map map ON map.id = um.map_id 
                        LEFT JOIN t_bjnz_work w ON q.user_id=w.id
                        where us.status=2 $where
                        ORDER BY adds.addtime ";
                if($page==1)
                {
                    $sql ="select count(*) as count from ($sql) t1";
                }
                else
                {
                    $sql = $sql.$limit;
                }
            }
            $result = $this->querylist($sql);
            return $result;
        }

        //网格巡视情况统计
        public function get_wangge($start_time,$end_time,$where,$limit='',$type=0,$page=0,$status='')
        {
            $left = $status!=''?'RIGHT':'LEFT';
            if($type == 0)
            {
                $sql ="select t1.*,IFNULL(t2.number,'无任务') number,IFNULL(t2.status,0) as status_num,IFNULL(t2.wwc,0) as wwc,IFNULL(t2.ywc,0) as ywc from
(select id,fuzeren,area from t_bjnz_map $where GROUP BY id) t1
$left JOIN
(SELECT
	map_id,sum(um.end_number) AS number,count(um.status) as status,count(if(um.status=1,true,null)) as wwc,count(if(um.status=2,true,null)) as ywc
FROM bjnz_new_quantification_user_map um
WHERE um.addtime >= $start_time AND um.addtime < $end_time $status
GROUP BY
	um.map_id) t2 ON t1.id=t2.map_id";
//                $sql ="select map.id,map.fuzeren,map.area,sum(um.end_number) as number from t_bjnz_map map left join bjnz_new_quantification_user_map um on map.id=um.map_id where um.addtime >= $start_time AND um.addtime < $end_time $where GROUP BY map.id";
            }
            else
            {
                $sql = "SELECT u.LoginName,map.w_bianhao,map.area,map.fuzeren,adds.addtime,um.map_id,u.PID, IF (q.user_type = 3, w. NAME, '无') AS gname, u.UserName, d. NAME AS bname FROM bjnz_new_quantification_user_map_add adds  LEFT JOIN bjnz_new_quantification_user_map um ON um.id=adds.qum_id LEFT JOIN bjnz_new_quantification_user us ON um.qu_user_id = us.id LEFT JOIN bjnz_new_quantification q ON us.qu_id = q.id  LEFT JOIN t_user u ON um.PID = u.PID LEFT JOIN t_enterprise_dept d ON u.DepartmentID = d.ID LEFT JOIN t_bjnz_work w ON q.user_id = w.id LEFT JOIN t_bjnz_map map ON um.map_id=map.id WHERE um.addtime >= $start_time AND um.addtime < $end_time $where $status";
                if($page==1)
                {
                    $sql ="select count(*) as count from ($sql) t1";
                }
                else
                {
                    $sql = $sql.$limit;
                }
            }
            $result = $this->querylist($sql);
            return $result;
        }
        //获取所有顶级部门
        public function get_dept_max()
        {
            $sql = "select ID,Name,ParentID from t_enterprise_dept where ParentID=0";
            $res = $this->querylist($sql);
            return $res;
        }
        //获取所有网格
        public function get_all_map()
        {
            $sql = "select id,area from t_bjnz_map";
            $res = $this->querylist($sql);
            return $res;
        }
        //根据部门获取所有人员
        public function get_pid($dept_id)
        {
            $sql = "select PID from t_user where DepartmentID in($dept_id)";
            $res = $this->querylist($sql);
            return $res;
        }
        /**
         * 检测该分类下所有子分类，并输出ID
         */
        public function getChildrenIds($sort_id){
            $ids = $sort_id;
            $sql = "SELECT ID FROM t_enterprise_dept WHERE ParentID =".$sort_id;
            $result = $this->querylist($sql);

            if ($result){
                foreach ($result as $key=>$val){
                    $ids .= ','.$val['ID'].",";
                    $ids .= $this->getChildrenIds ($val['ID']);
                }
            }

            $idsArr = explode(",",$ids);
            $idsArr = array_unique($idsArr);
            $ids = implode(",",$idsArr);
            return $ids;
        }

}